package com.slofzx.nuoda.evaluation.project.builder;

import com.alibaba.fastjson.JSON;
import com.slofzx.nuoda.common.util.DataConvertUtil;
import com.slofzx.nuoda.custom.template.enums.SumTypeEnum;
import com.slofzx.nuoda.evaluation.steretype.ExcelExportConfig;
import com.slofzx.nuoda.evaluation.steretype.ExcelExportItem;
import com.slofzx.nuoda.excel.builder.AbstractExcelBuilder;
import com.slofzx.nuoda.excel.entity.CellConfig;
import com.slofzx.nuoda.excel.entity.SheetConfig;
import com.slofzx.nuoda.excel.entity.TemplateConfig;
import com.slofzx.nuoda.excel.vo.ExcelPreviewDataVO;
import com.slofzx.nuoda.expression.ExpressionInvoker;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.ByteArrayInputStream;
import java.util.*;

/**
 * excel导出按行写入构造器（支持横向拓展）
 */
public abstract class AbstractExcelExtendBuilder extends AbstractExcelBuilder {

    protected List<Integer> yearList;



    public AbstractExcelExtendBuilder(TemplateConfig config) {
        super(config);
        // this.excelExportItemList = excelExportItemList;

    }

    public AbstractExcelExtendBuilder yearList(List<Integer> yearList) {
        this.yearList = yearList;
        return this;
    }





    @Override
    public Workbook build() {
        handleYearColumnExtend();
        return super.build();
    }

    /**
     * 行内数据计算公式执行
     * @param sheetConfig
     * @param dataMap
     */
    protected void computeExpression(SheetConfig sheetConfig, HashMap<String, Object> dataMap) {
        List<CellConfig> cellConfigList = sheetConfig.getCellConfigList();
        for (CellConfig item : cellConfigList) {
            if (!"expression".equals(item.getDataType()) || StringUtils.isBlank(item.getData())) {
                continue;
            }
            try {
                ExpressionInvoker invoker = new ExpressionInvoker(item.getData());
                if (invoker == null) {
                    continue;
                }
                dataMap.put(item.getData(), invoker.invoke(dataMap));
            } catch (Exception e) {
                logger.error("自定义公式计算失败：{},数据：{}", item.getData(), dataMap, e);
            }
        }
    }

    /**
     * 处理年份扩展列逻辑
     */
    protected void handleYearColumnExtend() {

        try {
            //对配置信息进行扫描，确定年度配置单元格
            Map<String, List<CellConfig>> yearCellConfigMap = new HashMap<>();

            List<SheetConfig> sheetConfigList = templateConfig.getSheetConfigList();
            for (SheetConfig sheetConfig : sheetConfigList) {
                if (sheetConfig == null || sheetConfig.getCellConfigList() == null || sheetConfig.getCellConfigList().isEmpty()) {
                    continue;
                }
                List<CellConfig> cellConfigList = sheetConfig.getCellConfigList();
                for (CellConfig item : cellConfigList) {
                    if ("year".equals(item.getDataType())) {
                        List<CellConfig> subList = yearCellConfigMap.get(sheetConfig.getName());
                        if (subList == null) {
                            subList = new ArrayList<>();
                            yearCellConfigMap.put(sheetConfig.getName(), subList);
                        }
                        subList.add(item);
                    }
                }
            }
            if (yearCellConfigMap.isEmpty()) {
                //如果没有年份配置数据，则不进行处理
                return;
            }
            //必须以只读方式打开
            Workbook workbook = null;

//            byte[] copyData = FileCopyUtils.copyToByteArray(templateConfig.getFile());
            byte[] fileData = templateConfig.getFileData();
            byte[] copyData = Arrays.copyOf(fileData, fileData.length);
            //不能直接用file进行创建，close时会回写文件污染模板
            workbook = WorkbookFactory.create(new ByteArrayInputStream(copyData));
            this.workbook = workbook;
//            DataFormat dataFormat = workbook.createDataFormat();
            //对目标列进行动态扩展
            for (SheetConfig sheetConfig : sheetConfigList) {
                List<CellConfig> yearConfigList = yearCellConfigMap.get(sheetConfig.getName());
                if (yearConfigList == null) {
                    continue;
                }

                Map<Integer, List<CellConfig>> columnIndexCellConfigMap = new HashMap<>();
                List<CellConfig> sheetCellConfigList = sheetConfig.getCellConfigList();
                Map<String, CellConfig> cellDataConfigMap = new HashMap<>();
                for (CellConfig cellDataConfig : sheetCellConfigList) {
                    cellDataConfigMap.put(cellDataConfig.getRowIndex() + "_" + cellDataConfig.getColIndex(), cellDataConfig);
                }
                for (CellConfig config : sheetCellConfigList) {
                    List<CellConfig> subList = columnIndexCellConfigMap.get(config.getColIndex());
                    if (subList == null) {
                        subList = new ArrayList<>();
                        columnIndexCellConfigMap.put(config.getColIndex(), subList);
                    }
                    subList.add(config);
                }
                Sheet sheet = workbook.getSheet(sheetConfig.getName());
                if (sheet == null) {
                    continue;
                }
                //获取合并区域，为后续自动合并表头准备
                List<CellRangeAddress> mergeRegionList = sheet.getMergedRegions();
                Map<String, CellRangeAddress> mergeRegionMap = new HashMap<>();
                //记录旧合并区域转换后的新合并区域
                Map<CellRangeAddress,CellRangeAddress> old2NewMergeRegionMap = new HashMap<>();
                Map<CellRangeAddress, Integer> mergeRegionIndexMap = new HashMap<>();
                if (mergeRegionList != null) {
                    int index = 0;
                    for (CellRangeAddress region : mergeRegionList) {
                        mergeRegionIndexMap.put(region,index++);
                        for (int rangeRow = region.getFirstRow(); rangeRow <= region.getLastRow(); rangeRow++) {
                            for (int rangeCol = region.getFirstColumn(); rangeCol <= region.getLastColumn(); rangeCol++) {
                                mergeRegionMap.put(rangeRow + "_" + rangeCol, region);
                            }
                        }
                    }
                }
                List<CellRangeAddress> newRegionList = new ArrayList<>();
                //对配置进行排序，以便后续按序扩展
                yearConfigList.sort((item1, item2) -> {
                    return item1.getColIndex() - item2.getColIndex();
                });
                //如果同一列有多个年份配置，则只保留一个
                Set<Integer> colIndexSet = new HashSet<>();
                List<CellConfig> validList = new ArrayList<>();
                for (CellConfig item : yearConfigList) {
                    if (colIndexSet.contains(item.getColIndex())) {
                        continue;
                    }
                    colIndexSet.add(item.getColIndex());
                    validList.add(item);
                }

                //动态扩展出的单元格配置
                List<CellConfig> newConfigList = new ArrayList<>();
                //需要移除的单元格配置
                List<CellConfig> removeConfigList = new ArrayList<>();
                Set<CellRangeAddress> removedRegionSet = new HashSet<>();
                for (int index = validList.size() - 1; index >= 0; index--) {
                    CellConfig yearCellConfig = validList.get(index);
                    int rawColIndex = yearCellConfig.getColIndex();

                    //对年份列进行动态扩展
                    int targetColIndex = rawColIndex + yearList.size() * index;

                    //向上查找单元格配置，确定是否需要合并单元格
                    int maxMergeRowIndex = yearCellConfig.getRowIndex() - 2;
                    for (int findRow = yearCellConfig.getRowIndex() - 2; findRow >= 0; findRow--) {
                        CellConfig findConfig = cellDataConfigMap.get(findRow + 1 + "_" + (rawColIndex - 1));
                        if (findConfig != null) {
                            maxMergeRowIndex = findRow - 1;
                        }
                    }
                    if (maxMergeRowIndex >= 0) {
                        for(int reginRow = maxMergeRowIndex;reginRow>=0;reginRow--) {
                            try {
                                CellRangeAddress rangeAddress = mergeRegionMap.get(reginRow + "_" + (rawColIndex - 1));
                                if (rangeAddress == null) {
                                    Row row = sheet.getRow(reginRow);
                                    if(row!=null) {
                                        newRegionList.add(new CellRangeAddress(reginRow, reginRow, targetColIndex - 1, targetColIndex + yearList.size() - 1));
                                    }
                                } else {
                                    int topRow = rangeAddress.getFirstRow();
                                    CellRangeAddress newRangeAdress = new CellRangeAddress(topRow, reginRow, rangeAddress.getFirstColumn() - (rawColIndex - 1) + targetColIndex - 1, rangeAddress.getLastColumn() - (rawColIndex - 1) + targetColIndex + yearList.size() - 1);
                                    reginRow = topRow;
                                    if (removedRegionSet.contains(rangeAddress) == false) {
                                        newRegionList.add(newRangeAdress);
                                        int removeRegionIndex = mergeRegionIndexMap.get(rangeAddress);
                                        sheet.removeMergedRegion(removeRegionIndex);
                                        mergeRegionIndexMap.remove(rangeAddress);
                                        old2NewMergeRegionMap.put(rangeAddress,newRangeAdress);
                                        for(Map.Entry<CellRangeAddress,Integer> entry:mergeRegionIndexMap.entrySet()) {
                                            if(entry.getValue()>=removeRegionIndex) {
                                                entry.setValue(entry.getValue()-1);
                                            }
                                        }
                                        removedRegionSet.add(rangeAddress);
                                    } else {
                                        CellRangeAddress tmp = old2NewMergeRegionMap.get(rangeAddress);
                                        if(tmp.getFirstColumn()>newRangeAdress.getFirstColumn()) {
                                            tmp.setFirstColumn(newRangeAdress.getFirstColumn());
                                        }
                                        if(tmp.getLastColumn()<newRangeAdress.getLastColumn()) {
                                            tmp.setLastColumn(newRangeAdress.getLastColumn());
                                        }
                                    }

                                }
                            } catch (Exception e) {
                                logger.error("添加合并区域失败",e);
                            }
                        }
                    }

                    List<CellConfig> subList = columnIndexCellConfigMap.get(rawColIndex);
                    Set<Integer> yearRowSet = new HashSet<>();
                    for (CellConfig item : subList) {
                        if ("year".equals(item.getDataType())) {
                            yearRowSet.add(item.getRowIndex() - 1);
                            removeConfigList.add(item);
                        } else {
                            String oldData = item.getData();
                            for (int yearIndex = 0; yearIndex <= yearList.size(); yearIndex++) {
                                if (yearIndex == 0) {
                                    item.setColIndex(targetColIndex);
                                    if (StringUtils.isNotEmpty(oldData)) {
                                        SumTypeEnum sumTypeEnum = SumTypeEnum.getEnum(item.getSumType());
                                        String suffix = ":";
                                        if(sumTypeEnum!=null) {
                                            switch (sumTypeEnum) {
                                                case SUM:
                                                    suffix += "sum";
                                                    break;
                                                case AVG:
                                                    suffix += "avg";
                                                    break;
                                                case MAX:
                                                    suffix += "max";
                                                    break;
                                                case MIN:
                                                    suffix += "min";
                                                    break;
                                            }
                                        }
                                        item.setData(oldData + suffix);
                                    }
                                } else {
                                    CellConfig tmp = JSON.parseObject(JSON.toJSONString(item), CellConfig.class);
                                    tmp.setColIndex(targetColIndex + yearIndex);
                                    if (StringUtils.isNotEmpty(oldData)) {
                                        tmp.setData(oldData + ":" + yearIndex);
                                    }
                                    newConfigList.add(tmp);
                                }
                            }
                        }
                    }
                    for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row == null) {
                            continue;
                        }
                        moveRightCellData(sheet, rowIndex, rawColIndex - 1, yearList.size() + 1, cellDataConfigMap);
                        CellConfig cellConfig = cellDataConfigMap.get(rowIndex + 1 + "_" + rawColIndex);
                        Cell baseCell = row.getCell(rawColIndex - 1);
                        for (int yearIndex = 0; yearIndex <= yearList.size(); yearIndex++) {

                            Cell cell = row.getCell(rawColIndex - 1 + yearIndex);
                            if (cell == null) {
                                cell = row.createCell(rawColIndex - 1 + yearIndex);
                                if (baseCell != null) {
                                    cell.setCellStyle(baseCell.getCellStyle());
                                }
                            }
                            if (cellConfig != null) {
                                String cellValue = "$";
                                if ("year".equals(cellConfig.getDataType())) {
                                    if (yearIndex == 0) {
                                        cellValue = "合计";
                                    } else {
                                        cellValue = "第" + yearIndex + "年";
                                    }
                                }
                                cell.setCellValue(cellValue);
                            }
                        }


                    }

                }
                sheetConfig.getCellConfigList().addAll(newConfigList);
                sheetConfig.getCellConfigList().removeAll(removeConfigList);
                for (CellRangeAddress regin : newRegionList) {
                    try {
                        sheet.addMergedRegion(regin);
                    }catch (Exception e){
                        logger.error("自定义合并导出添加合并区域异常:{},{}:{},{}",regin.getFirstRow(),regin.getFirstColumn(),regin.getLastRow(),regin.getLastColumn(),e);
                    }
                }

            }
//            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
//            workbook.write(outputStream);
//            templateConfig.setFileData(outputStream.toByteArray());
//            outputStream.close();
//            workbook.close();
        } catch (Exception e) {
            logger.error("合并导出年份列扩展失败", e);
        }
    }

    /**
     * 生成预览数据
     *
     * @return
     */
    @Override
    public ExcelPreviewDataVO preview() {
        handleYearColumnExtend();
        return super.preview();
    }
}
